package com.jywl.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.servlet.ServletFileUpload;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;


public class ExcelUtil {
	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	
	public static final SimpleDateFormat DATA_FORMAT=new SimpleDateFormat("yyyy/MM/dd");
	public static Map<String, Class<?>> getMeClass(Class<?> c){
		Map<String, Class<?>> map = new HashMap<String, Class<?>>();
		Method[] methods = c.getDeclaredMethods();// 得到方法
		for (Method method : methods) {
		Class<?>[] parameterTypes = method.getParameterTypes();
		if(parameterTypes.length==1){
			 map.put(method.getName(), parameterTypes[0]);
		}
		
		}
		return map;
		
	}
	/**根据模板和数据填充excel并且返回路径
	 * @param modelpath
	 * @param datas
	 * @return
	 * @throws IOException 
	 */
	public static <E> String  exportExcelByModel(String modelname,List<E> datas,String realName) throws Exception{
		
		return exportExcelByModel(modelname, datas,null,null,realName);
		 
	}
	
	
	
	/**根据模板和数据填充excel 如何传入请求信息则输入到response 否则输入到本地
	 * @param modelname 模板
	 * @param datas
	 * @param request
	 * @param response
	 * @param realName
	 * @return
	 * @throws Exception
	 */
	public static <E> String  exportExcelByModel(String modelname,List<E> datas,HttpServletRequest request,  
            HttpServletResponse response,  
            String realName) throws Exception{
		
		String filePath = "";
		if(null== datas||datas.size()==0){
			String result = "<script type=\"text/javascript\">\n"+"parent.alert('没有数据');\n</script>";
         	response.setCharacterEncoding("utf-8");
         	response.getWriter().write(result);
         	response.getWriter().flush();
			return filePath;
		}
	   if(null!=request&&null!=response){
		   filePath = FileUtil.getTempFolder();
			 File dirname = new File(filePath); 
				if (!dirname.isDirectory()) { //目录不存在 
					dirname.mkdirs(); //创建目录
				} 	
				filePath =dirname.getAbsolutePath()+File.separator+CommUtil.UUid()+".xlsx";
		}
		
		String modelpath= ContextLoader.getCurrentWebApplicationContext() .getServletContext().getRealPath("/resources/excel/"+modelname);		 
		Workbook wb = null;
		FileInputStream	is =null;
	    BufferedOutputStream bos = null;  
	    FileOutputStream out = null;
		 try {
			is=new FileInputStream(modelpath);
			wb = new XSSFWorkbook(is);
			Sheet sheet = wb.getSheetAt(0);      
            List<Method> mothds = new ArrayList<Method>();
            List<String>  mothdsRet = new ArrayList<String>();
            //第三行是属性好
            Row row = sheet.getRow(0);
            int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
            //设置方法和返回类型
            for (int k = 0; k < cellSize; k++) {
                Cell cell = row.getCell(k);
                Method m =  datas.get(0).getClass().getMethod("get"+GeneratorUtil.captureName(cell.toString()));
                mothds.add(m);
                mothdsRet.add(m.getReturnType().getName());
            }
            sheet.removeRow(row);
            int l = datas.size();
            for(int i=2;i<l+2;i++){
            	E e =datas.get(i-2);
            	//创建行
            	Row r = sheet.getRow(i);
            	if(null ==r){
            		r=sheet.createRow(i);
            	}
            	for(int j =0;j<cellSize;j++){
            		Cell c = r.getCell(j);
            		if(null ==c ){
            			c=r.createCell(j);
            		}
					Object o =  mothds.get(j).invoke(e);
					if(null!=o){
						switch (mothdsRet.get(j)) {
						case "java.lang.String":
							c.setCellValue(null!=o?o.toString():"");
							break;
						case "java.lang.Integer":
							
							c.setCellValue((Integer)o);
							break;
						case "java.lang.Long":
							c.setCellValue((Long)o);
							break;
						case "java.lang.Double":
							c.setCellValue((Double)o);
							break;
						case "java.util.Date":
							c.setCellValue(DATA_FORMAT.format((Date)o));
							break;
								
						default:
							c.setCellValue(null!=o?o.toString():"");
							break;
						}
					}
            	}
            }
            if(null!=request&&null!=response){
            	 request.setCharacterEncoding("UTF-8");  
                 response.setContentType("application/octet-stream");  
                 response.setHeader("Content-disposition", "attachment; filename="  
                         + new String(realName.getBytes("utf-8"), "ISO8859-1"));  
                 bos = new BufferedOutputStream(response.getOutputStream());  
                 wb.write(bos);  
            }else{
            	out = new FileOutputStream(filePath);
                wb.write(out);  
            }
           
		}  finally {
	        if (wb != null) {
	           try {wb.close();} catch (Exception e2) {e2.printStackTrace();} 
	        }
	        if (is != null) {
	        	try {is.close();} catch (Exception e2) {e2.printStackTrace();} 
	        }
	        if (bos != null) {
	        	try {bos.close();} catch (Exception e2) {e2.printStackTrace();} 
	        }if (out != null) {
	        	try {bos.close();} catch (Exception e2) {e2.printStackTrace();} 
	        	
	        }
	    }
		
		return filePath; 
	}
	/**
	 * 适用于第一行是方法名，例如
	 * 第二行是标题的excel
	 * setName setAge 
	 * 姓名   年龄
	 * 张三   25  
	 * 李四   22
	 * @throws Exception 
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static <T> List<T>
	readExcelWithTitle(String filepath,Class<T> c) throws Exception{
	    Map<String, Class<?>> mmap = ExcelUtil.getMeClass(c);
	    try(InputStream is = new FileInputStream(filepath);
	        Workbook wb =  new XSSFWorkbook(is); ) {
	       
			List  result = new ArrayList<>();//对应excel文件
            Sheet sheet = wb.getSheetAt(0);
            List<String> hiddenMothds = new ArrayList<String>();//放置所有隐藏
            List<String> titles = new ArrayList<String>();//放置所有的标题
            int rowSize = sheet.getLastRowNum() + 1;
            for (int j = 0; j < rowSize; j++) {//遍历行
                Row row = sheet.getRow(j);
                if (row == null) {//略过空行
                    continue;
                }
                int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                if (j == 0) {//隐藏行 字段名
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        hiddenMothds.add("set"+  GeneratorUtil.captureName(cell.toString()));
                    }
                } else if(j == 1){
                	for (int p = 0; p < cellSize; p++) {
                        Cell cell = row.getCell(p);
                        titles.add(cell.toString());
                    }
                } else {//其他行是数据行
                	Object o = c.newInstance();
                    for (int k = 0; k < hiddenMothds.size(); k++) {
                        Cell cell = row.getCell(k);
                        String mName = hiddenMothds.get(k);
                        Method m =  c.getMethod(mName, new Class[] { mmap.get(mName) });
                        Object[] arguments =null;
                        String title = titles.get(k);
                       if ( mmap.get(mName).getName().equals("java.lang.Short")) {
                    	   if(cell.getCellTypeEnum()!=CellType.NUMERIC){
                    		   throw new Exception("【"+title+"】:"+cell.toString()+"--不是数字型");
                    	   }
                    	   arguments=  new Object[] { 
                    			  (short)(cell.getNumericCellValue()) 	                			   
                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.lang.Long")) {
                    	   if(cell.getCellTypeEnum()!=CellType.NUMERIC){
                    		   throw new Exception("【"+title+"】:"+cell.toString()+"--不是数字型");
                    	   }
                    	   arguments=  new Object[] { 
	                    			  (long)(cell.getNumericCellValue()) 	                			   
	                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.lang.Integer")) {
                    	   if(cell.getCellTypeEnum()!=CellType.NUMERIC){
                    		   throw new Exception("【"+title+"】:"+cell.toString()+"--不是数字型");
                    	   }
                    	   arguments=  new Object[] { 
	                    			  (int)(cell.getNumericCellValue()) 	                			   
	                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.lang.Double")) {
                    	   if(cell.getCellTypeEnum()!=CellType.NUMERIC){
                    		   throw new Exception("【"+title+"】:"+cell.toString()+"--不是数字型");
                    	   }
                    	   arguments=  new Object[] { 
	                    			cell.getNumericCellValue()	                			   
	                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.lang.Boolean")) {
                    	   arguments=  new Object[] { 
	                    			cell.getBooleanCellValue()              			   
	                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.util.Date")) {
                    	   	arguments=  new Object[] { 
	                    			 cell.getDateCellValue()	                			   
	                    	   };
                       }else if ( mmap.get(mName).getName().equals("java.lang.String")) {
                    	   if(cell.getCellTypeEnum()==CellType.NUMERIC){
                    		   arguments=  new Object[] { 
                    				 String.valueOf((long)cell.getNumericCellValue())              			   
    	                    	   };
                    	   }else{
                    		   arguments=  new Object[] { 
  	                    			 cell.getStringCellValue()            			   
  	                    	   };
                    	   }
                   	   	
                      }
                       else{
                    	   arguments = new Object[] { cell.toString() };
                       }
                      
                        m.invoke(o, arguments);
                    }
                    result.add(o);
                }
            }
	        return result;
	    } catch (FileNotFoundException e) {
	        throw new Exception("未发现相关文件"+e.getMessage());
	    } catch (IOException e) {
	    	 throw new Exception("不是Excel类型"+e.getMessage());
		} catch (InstantiationException e) {
			 throw new Exception("解析excel失败"+e.getMessage());
		} catch (NoSuchMethodException e) {
			 throw new Exception("模板与实体类不匹配"+e.getMessage());
		} catch (Exception e) {
			 throw new Exception("系统错误："+e.getMessage());
		} 
		
	}
}
